/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.dao;

import br.com.classes.Cheque;
import br.com.classes.PesquisaCheque;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author Eziquiel
 */
public class ChequeDao extends Conecta {

    Connection con = null;

    public ChequeDao() {
        con = Conecta.getConnection();
    }

    public void CadastrarCheque(Cheque c, int cod) throws Exception {

        String sql = ("insert into cheque values(null,?,?,?,?,?,?,?,?,?,?,null)");
        PreparedStatement prep = con.prepareStatement(sql);
        
        prep.setString(1, c.getComp());
        prep.setString(2, c.getBanco());
        prep.setString(3, c.getAgencia());
        prep.setString(4, c.getConta());
        prep.setString(5, c.getNum_cheque());
        prep.setDouble(6, c.getValor_cheque());
        prep.setString(7, c.getData());
        prep.setString(8, c.getDevolvido());
        prep.setString(9, c.getEmitente());  
        prep.setInt(10, cod);       
//        if (c.getSaida_ck() == -1){
//             prep.setString(11, null);
//        } else {
//           prep.setInt(11, c.getSaida_ck());
//        }
        prep.execute();
    }

    public void AlterarCheque(Cheque c) throws Exception {

        try {

            String sql = ("UPDATE  cheque SET  comp = ?,banco =? ,agencia = ? ,conta =  ?,num_cheque = ?,valor_cheque =? ,data =? ,devolvido = ?,emitente = ? WHERE cod_cheque =?");
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, c.getComp());
            ps.setString(2, c.getBanco());
            ps.setString(3, c.getAgencia());
            ps.setString(4, c.getConta());
            ps.setString(5, c.getNum_cheque());
            ps.setDouble(6, c.getValor_cheque());
            ps.setString(7, c.getData());
            ps.setString(8, c.getDevolvido());
            ps.setString(9, c.getEmitente());
       //     ps.setInt(10, c.getEntrada_ck());
         //   ps.setInt(11, c.getSaida_ck());
            ps.setInt(10, c.getCod_cheque());
            ps.execute();

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Funçao AlterarCheque! "+ e.getMessage());
        }
    }
    
         public void AlterarSaidaCheque(int cheque, int saida) {

        try {
            String sql = ("UPDATE cheque SET saida_codmov=? WHERE cod_cheque =?");
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, saida);
            ps.setInt(2, cheque);
            System.out.println("SQL 2 - "+ps);
            ps.execute();
          } catch (SQLException ex) {        
            JOptionPane.showMessageDialog(null, "Funçao AlterarSaidaCheque! "+ ex.getMessage());      
        }
    }
         
        public void AlterarSaidaChequeParaNULL(int cod_cheque) { // altera a saida do cheque para null, quando quiser retirar o mesmo de um movimento de saida

        try {
            String sql = ("UPDATE cheque SET saida_codmov = NULL WHERE cod_cheque =?");   
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, cod_cheque);
            System.out.println("SQL  "+ps);
            ps.executeUpdate();
          } catch (SQLException ex) {         
            JOptionPane.showMessageDialog(null, "Funçao AlterarSaidaChequeParaNULL! "+ ex.getMessage());        
        }
    }
    

    public void DeletarCheque(int cod_cheque) throws Exception {

        String sql = ("delete from cheque where cod_cheque = ?");
        PreparedStatement prep = con.prepareStatement(sql);

        prep.setInt(1, cod_cheque);
        prep.execute();
    }

    public List<Cheque> ProcurarChequeLetra(String cheque) {

        List<Cheque> lista = new ArrayList<Cheque>();

        try {
            String sql = "select * from cheque where emitente like ?";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "%" + cheque + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {

                Cheque che = new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                lista.add(che);
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Funçao ProcurarChequeLetra! "+ ex.getMessage());
        }
        return lista;
    }

    public List<Cheque> ListarCheque() {

        List<Cheque> lista = new ArrayList<Cheque>();

        try {
            String sql = "select * from cheque order by cod_cheque desc";
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Cheque che = new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                lista.add(che);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }

    public Cheque PesquisarCheque(int cod_cheque) {

        Cheque che = null;
        try {
            String sql = "select * from cheque where cod_cheque=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, cod_cheque);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                che= new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                che.setStatus("Excluir");
            }
        } catch (SQLException d) {
        }
        return che;
    }
    
        public ArrayList PesquisarChequeMoviimento(int cod_movimento) {
        
        ArrayList<Cheque> ChequesPesquisados = new ArrayList<Cheque>();
        Cheque che = null;
        try {
            String sql = "select * from cheque where entrada_codmov=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, cod_movimento);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                che= new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                che.setStatus("Cadastrado");
                ChequesPesquisados.add(che);
            }
        } catch (SQLException d) {
        }
        return ChequesPesquisados;
    }
    

    public int UltimoCodCheque() {

        int cod = 0;

        try {
            String sql = "select max(cod_cheque) + 1 as codigo from cheque";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                cod = rs.getInt("codigo");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage()+"Dao Funcao UltimoCodCheque");
        }
        return cod;
    }

    public List<Cheque> BuscarLimiteCheque(int num) {

        List<Cheque> lista = new ArrayList<Cheque>();

        try {
            String sql = "select * from cheque order by cod_cheque desc limit ?,10";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, num);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Cheque che = new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                lista.add(che);
            }

        } catch (SQLException ex) {
        }
        return lista;
    }

    public int PesquisarChequeMov(int cod_cheque) {

        int compara = 0;

        try {
            String sql = "SELECT cod_cheque FROM  cheque where cod_cheque = ? ";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, cod_cheque);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                if (rs.equals(0)) {
                    compara = 0;
                } else {
                    compara = 1;
                }
            }
        } catch (SQLException d) {
            System.out.println(d.getMessage()+ "Dao Funcao PesquisaChequeMov");
        }
        return compara; // na outra tela onde retornamos esse valor e mostramos a mensagem
    }
    
     public List<Cheque> ListarChequeMov() {

        List<Cheque> lista = new ArrayList<Cheque>();

        try {
            String sql = "select * from cheque where saida_codmov is null";
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Cheque che = new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                lista.add(che);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }
    
     public ArrayList BuscarChequeMovDestino(int ckmov) {

        ArrayList<Cheque> ChequesPesquisados = new ArrayList<Cheque>();
        Cheque che = null;
        try {
            
            String sql = "select * from cheque where saida_codmov=?";         
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, ckmov);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                che= new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                che.setStatus("Cadastrado");
                ChequesPesquisados.add(che);
            }
        } catch (SQLException d) {
        }
        return ChequesPesquisados;
    }
     
          public ArrayList BuscarChequeMovOrigem(int ckmov) {

        ArrayList<Cheque> ChequesPesquisados = new ArrayList<Cheque>();
        Cheque che = null;
        try {
            String sql = "select * from cheque where entrada_codmov=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, ckmov);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                che= new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));
                che.setStatus("Cadastrado");
                ChequesPesquisados.add(che);
            }
        } catch (SQLException d) {
        }
        return ChequesPesquisados;
    }
          
          
    public String ListarEntidadeNomePorCheque(int cod_cheque) {

        String nome_ent = "";
        try {
            String sql = "select entidade.nome_ent from cheque INNER JOIN MOVIMENTO on movimento.cod_movimento = cheque.entrada_codmov inner join entidade on entidade.cod_ent = movimento.mov_entidade where cheque.cod_cheque = ?";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, cod_cheque);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                nome_ent = rs.getString("nome_ent");
            }
        } catch (SQLException e) {
        }
        return nome_ent;
    }
    
        public boolean MudaChequeNull(){

        try {
            String sql = "ALTER TABLE  `cheque` CHANGE  `data`  `data` DATE NULL DEFAULT NULL";
            PreparedStatement stmt = con.prepareStatement(sql);
            int rs = stmt.executeUpdate();
           
        } catch (SQLException e) {
        }
       return true;
    }
        

      public List<Cheque> PesquisarChequeAvancada(PesquisaCheque e) {

        List<Cheque> lista = new ArrayList<Cheque>();
        Cheque che;
        try {
            String sql = "SELECT * FROM  `cheque` "+
"inner join movimento as entrada on entrada.cod_movimento = entrada_codmov "+
"inner join movimento as saida on saida.cod_movimento = saida_codmov "+
"WHERE  1=1 ";
if(e.getCod_cheque()!=0){
    sql +="and `cod_cheque` =? ";
}            
if(!e.getComp().equals("")){
    sql +="AND  `comp` LIKE ? ";
}
if(!e.getBanco().equals("")){
    sql +="AND  `banco` LIKE ?";
}
if(!e.getAgencia().equals("")){
sql +="AND  `agencia` LIKE  ?";  
}
if(!e.getConta().equals("")){
sql +="AND  `conta` LIKE  ?";
}
if(e.getNum_cheque().equals("")){
sql +="AND  `num_cheque` LIKE  ?";
}
if(!e.getValor_cheque().equals("")){
sql +="AND  `valor_cheque` = ?";
}
if(!e.getDataini().equals("") ){
sql +="AND  cheque.data between  ?";
}
if(!e.getDataFim().equals("") ){
sql +="and ?";
}
if(!e.getDevolvido().equals("")){
sql +="AND  `devolvido` LIKE  ?";
}
if(!e.getEmitente().equals("")){
sql +="AND  `emitente` LIKE  ?";
}
if(e.getEntrada_ck()!=0){
sql +="AND  `entrada_codmov` =?";
}
if(e.getSaida_ck()!=0){
sql +="AND  `saida_codmov` =?";
}
if(!e.getDataEntradaIni().equals("")){
sql +="AND entrada.data between ?";
}
if(!e.getDataEntradaFim().equals("")  ){
sql +="and ?";
}
if(!e.getDataSaidaIni().equals("") ){
 sql +="AND saida.data between ?";
}
if(e.getDataSaidaFim().equals("")  ){
 sql +="and ?";
}

 PreparedStatement ps = con.prepareStatement(sql);
            
            int x=0;
if(e.getCod_cheque() !=0){
    x++;
  ps.setInt(x, e.getCod_cheque());
} 
if(!e.getComp().equals("")){
     x++;
  ps.setString(x, e.getComp());
}
if(!e.getBanco().equals("")){
     x++;
  ps.setString(x, e.getBanco());
}
   System.out.println(ps);
      ResultSet rs = ps.executeQuery();
        System.out.println(rs);
      if(rs == null){
     
      }   
      while (rs.next()) {
                che= new Cheque();
                che.setCod_cheque(rs.getInt("cod_cheque"));
                che.setComp(rs.getString("comp"));
                che.setBanco(rs.getString("banco"));
                che.setAgencia(rs.getString("agencia"));
                che.setConta(rs.getString("conta"));
                che.setNum_cheque(rs.getString("num_cheque"));
                che.setValor_cheque(rs.getDouble("valor_cheque"));
                che.setData(rs.getString("data"));
                che.setDevolvido(rs.getString("devolvido"));
                che.setEmitente(rs.getString("emitente"));
                che.setEntrada_ck(rs.getInt("entrada_codmov"));
                che.setSaida_ck(rs.getInt("saida_codmov"));               
                lista.add(che);
            }
            
            
        } catch (SQLException d) {
        }
        return lista;
    }

          
}